![[dbt] データモデルの開発が便利になる「Package」を使ってみた（dbt_utils）](https://devio2023-media.developers.io/wp-content/uploads/2020/08/d72f41d6eb099a8fa0ef2791ad2b18a7.png)
[dbt] データモデルの開発が便利になる「Package」を使ってみた（dbt_utils）
大阪オフィスの玉井です。
dbtにはPackageというものがあります。プログラミングでいうライブラリみたいなものです。例えば、直接SQLで書こうとすると面倒な処理を、関数一発でできるようにするもの等があります。
今回は、一番メジャーなPackageであるdbt_utilsを使ってみました。
Packageのインストール方法
packageは、dbtプロジェクト毎に入れる形となります。ですので、プロジェクト単位で準備する必要があります。
packages.ymlを用意する
まず、プロジェクトにpackages.ymlを作成します。ここにインストールしたいPackageを記載していきます。作成場所の決まりとして、dbt_project.ymlと同じ階層に作る必要があります。
インストールしたいPackageを記述する
「どんなPackageがあるのか？」ですが、下記にまとめられています（dbt公式）。このサイトから、欲しいPackageを選びます。
今回インストールするのは、dbt_utilsです。データモデルを開発する上で、役に立つマクロやテスト等が沢山入っています。
上記もそうですが、Package毎のページにInstallationという見出しがあり、そこに従えば、Packageをインストールすることができます。dbt_utilsの場合は、packages.ymlに下記のように記述します。
packages:
- package: fishtown-analytics/dbt_utils
version: 0.6.4
Packageを実際にインストールする
packages.ymlに入れたいPackageを記述し終えたら、実際にインストールします。インストールするには、dbt depsコマンドを実行します。
$ dbt deps Running with dbt=0.18.1 Installing fishtown-analytics/dbt_utils@0.6.4 Installed from version 0.6.4
正常にインストールが完了すれば、プロジェクト下にPackage一式がインストールされます。
いくつか試してみた
dbt_utilsは非常に多くのマクロ等が組み込まれています。それぞれの使い方は、上記に載せている公式ドキュメントに一通り載っていますが、自分の環境でいくつか実際に使ってみました。
検証環境
- macOS 10.15.7 Catalina
- dbt CLI 0.19.0
- Google BigQuery
- （ちょっとだけ）Snowflake
datediff
日付の減算ができます。
「SQLでもすぐできるやん」ってなりそうですが、このマクロを使うことで、DBやDWHが変わっても、方言の差をdbt側が吸収してくれるようになります。また、開発者がいちいち「このDWHのdatediffってどう書くんだっけ…」となり、リファレンスをいちいち調べる必要も無くなります。
例として、以下のデータモデルを生成します。今年の元旦〜2月8日までの日数を算出します。
select
{{ dbt_utils.datediff("'2021-01-01'", "'2021-02-8'", 'day') }} as date_diff
from ...
BigQuery
生成されたデータモデルは下記の通り（テーブルの件数分、計算が出力されています）。
実行されたクエリは下記の通り。ちゃんとBigQueryの日付クエリが使われています。
Snowflake
今度は同じデータモデルを、Snowflakeで実行します。
生成されたデータモデルは下記の通り。
実行されたクエリは下記の通り。同じ内容のデータモデルでも、ちゃんとSnowflakeの日付計算クエリが使われていますね。
date_spine
連続した日付データを、指定した単位（日、月など）で生成できます。
例えば、下記のデータモデルは、日単位のデータを、2020年1月1日〜2021年1月1日の期間分、生成します。
{{ dbt_utils.date_spine(
datepart="day",
start_date="cast('2020-01-01' as date)",
end_date="cast('2021-01-01' as date)"
)
}}
order by date_day asc
生成されたデータモデルは下記の通り。指定した期間分、日付単位でレコードが生成されています（DATETIME型）。
実際に実行されたクエリは下記の通り。
…長いので、下記にペーストします（多少整形しています）。複雑なクエリが流れていることがわかります。
CREATE OR REPLACE TABLE `tamai-rei`.`jaffle`.`trial_2`
OPTIONS() AS(
WITH rawdata AS(
WITH p AS(
SELECT
0 AS generated_number
UNION ALL
SELECT
1
),
unioned AS(
SELECT
p0.generated_number * pow(2, 0) + p1.generated_number * pow(2, 1) + p2.generated_number * pow(2, 2) + p3.generated_number * pow(2, 3) + p4.generated_number * pow(2, 4) + p5.generated_number * pow(2, 5) + p6.generated_number * pow(2, 6) + p7.generated_number * pow(2, 7) + p8.generated_number * pow(2, 8) + 1 AS generated_number
FROM
p AS p0 CROSS
JOIN p AS p1 CROSS
JOIN p AS p2 CROSS
JOIN p AS p3 CROSS
JOIN p AS p4 CROSS
JOIN p AS p5 CROSS
JOIN p AS p6 CROSS
JOIN p AS p7 CROSS
JOIN p AS p8
)
SELECT
*
FROM
unioned
WHERE
generated_number <= 366
ORDER BY
generated_number
),
all_periods AS(
SELECT
(datetime_add(cast(cast('2020-01-01' AS date) AS datetime), interval row_number() over(ORDER BY 1) - 1 day)) AS date_day
FROM
rawdata
),
filtered AS(
SELECT
*
FROM
all_periods
WHERE
date_day <= cast('2021-01-01' AS date)
)
SELECT
*
FROM
filtered
ORDER BY
date_day ASC
)
pivot
BIツール用のデータマートを作っている時などで、頻繁にやる処理の一つが「ピボット」ではないでしょうか。このマクロを利用すれば、ピボット処理も簡単にできます。
例として、こちらの注文テーブルをピボットします。このデータを元にして、STATUS毎の件数を日付別に集計するという要件を想定します。
実行するデータモデルは下記の通り。dbt_utils.pivotの中で、もう一つ、dbt_utils.get_column_valuesというマクロが使われています。このマクロは、特定のカラムのユニーク値を全部取得できます。そして、そのユニーク値を使って（横に展開する）、ピボット処理を実現しています。
select
order_date,
{{ dbt_utils.pivot(
'status',
dbt_utils.get_column_values(source('jaffle','raw_order') , 'status')
) }}
from {{ source('jaffle','raw_order') }}
group by order_date
上記を実行して生成されたデータモデルは下記の通り。statusの値ごとにカラムが横展開されています。
実際に実行されたクエリは下記の通りです。
CREATE OR REPLACE TABLE `tamai-rei`.`jaffle`.`trial_3`
OPTIONS()
AS(
SELECT
order_date,
SUM(
CASE
WHEN status = 'completed' THEN 1
ELSE 0
END
) AS `completed`,
SUM(
CASE
WHEN status = 'placed' THEN 1
ELSE 0
END
) AS `placed`,
SUM(
CASE
WHEN status = 'shipped' THEN 1
ELSE 0
END
) AS `shipped`,
SUM(
CASE
WHEN status = 'returned' THEN 1
ELSE 0
END
) AS `returned`,
SUM(
CASE
WHEN status = 'return_pending' THEN 1
ELSE 0
END
) AS `return_pending`
FROM
`tamai-rei`.`jaffle`.`raw_order`
GROUP BY
order_date
)
頑張れば書けないことはないクエリですが、（手でピボットするクエリを実装した場合）ピボットするカラムのユニーク値が増えるほどCASE文が増えるため、データモデルのクエリ自体の可読性は酷い有様になるでしょう。また、ユニーク値が増えるたびに、そのデータモデル（のクエリ）のメンテナンスが必要になるため、運用も面倒になります。
ちなみにunpivotもあります。興味のある方は是非試してみてください。
equal_rowcount
「dbt_utils」は、マクロだけでなく、テストも新しいものが追加されます。
equal_rowcountは、2つのデータモデルの件数が一致するかどうかをテストします。
例えば、dim_customersというデータモデルがあったとします。これは、顧客データに、いくつか分析するためのデータを付与したモデルです。ですので、元の顧客データ（rawデータ）と件数が一致していないとおかしい（モデルを生成する過程のどこかで処理を誤っている）、ということになります。それを、このテストでチェックしてみます。
テストは下記のように記述します。
- name: dim_customers
tests:
- dbt_utils.equal_rowcount:
compare_model: source('jaffle','raw_customer')
テストを実行してみます。合格しました。
$ dbt test --models dim_customers Running with dbt=0.19.0 Found 8 models, 12 tests, 0 snapshots, 1 analysis, 327 macros, 0 operations, 1 seed file, 2 sources, 0 exposures 13:01:56 | Concurrency: 4 threads (target='learn') 13:01:56 | 13:01:56 | 1 of 1 START test dbt_utils_equal_rowcount_dim_customers_source_jaffle_raw_customer_ [RUN] 13:01:59 | 1 of 1 PASS dbt_utils_equal_rowcount_dim_customers_source_jaffle_raw_customer_ [PASS in 3.05s] 13:01:59 | 13:01:59 | Finished running 1 test in 4.28s. Completed successfully Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
実際に実行されたテストクエリはこちら。互いの総件数を出して引き算、結果が0件だったら合格…というロジックですね。
おわりに
今回試したマクロやテストはほんの一部ですので、是非他のやつも使ってみたいです。そして、Package自体も、これ以外にもたくさんあるので、それらも試してブログにしたいと思います。
